
package de.imise.ur.service;

import java.sql.DriverManager;
//import java.util.Date;
import java.util.Properties;

import org.jdom.Element;
import org.springframework.stereotype.Service;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.*;
//import java.lang.*;

//import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException; 
import java.security.MessageDigest; 
import java.security.NoSuchAlgorithmException; 

/**
 * Simple stub implementation of {@link HumanResourceService}, which does nothing but logging.
 *
 * @author Arjen Poutsma
 */
@Service
public class StubCreateUserService implements CreateUserService {

    private static final Log logger = LogFactory.getLog(StubCreateUserService.class);
    
    private String m_driver = null;
    private String m_dburl = null;
    private String m_dbuser = null;
    private String m_dbpasswd = null;
    
    Connection m_postGresConn = null;
    
    private String m_pw = null;
    private String m_user_id = null;
	private String m_study_id = null;

    public Element createNewUser(String username, String firstname, String lastname,
    							String email, String institut, String role, String type,
    							String soap, String password, String study) {
    	
    		//load properties
            Properties prop = new Properties();
        	try {
        		//prop.load( new FileInputStream( path + "/config.properties" ) );
        		prop.load( StubCreateUserService.class.getClassLoader().getResourceAsStream( "config.properties" ) );
        		
        		logger.info( "INFO: " + StubCreateUserService.class.getClassLoader() );
        		
        		m_driver = prop.getProperty( "driver" );
        		m_dburl = prop.getProperty( "dburl" );
        		m_dbuser = prop.getProperty( "dbuser" );
        		m_dbpasswd = prop.getProperty( "dbpasswd" );
        	} catch (IOException ex) {
        		logger.info( "property error: " + ex );
        		return createResponse( "Failed to load properties: \n" + ex );
        	}

        	//encrypt password
            try {
    			m_pw = SHA1(password);
    		} catch (NoSuchAlgorithmException e1) {
    			return createResponse( "failed to encrypt password: \n" + e1 );
    		} catch (UnsupportedEncodingException e1) {
    			return createResponse( "failed to encrypt password: \n" + e1 );
    		}

        	//load driver and create connection
            try {
            	//Class.forName("org.postgresql.Driver");
            	Class.forName( m_driver );
            	//postGresConn = DriverManager.getConnection( "jdbc:postgresql://127.0.0.1:5432/openclinica", "clinica" , "openclinica" );
            	m_postGresConn = DriverManager.getConnection( m_dburl, m_dbuser , m_dbpasswd );
            	m_postGresConn.setAutoCommit(false);
            	
            	/*************************************************************************************/

            //getStudyID
        		Statement stGetStudyID = m_postGresConn.createStatement();
        		ResultSet rssid = stGetStudyID.executeQuery( "SELECT study_id FROM study WHERE unique_identifier = '" + study + "'" ); 
        		if( rssid.next() ){
        			m_study_id = rssid.getString(1);
        		} else {
        			return createResponse( "study-id '"+ study + "' does not exist: \n" );
        		}
        		stGetStudyID.close();
        		rssid.close();

        	//getUserID
        		Statement stGetUserID = m_postGresConn.createStatement();
        		ResultSet rsuid = stGetUserID.executeQuery( "SELECT MAX(user_id) FROM user_account" );
        		rsuid.next();
        		int tmp = Integer.parseInt( rsuid.getString(1) ) + 1;
        		m_user_id = String.valueOf(tmp);
        		stGetUserID.close();
        		rsuid.close();

        	//check multiple username
        		Statement stCheckUsername = m_postGresConn.createStatement();
        		ResultSet rscu = stCheckUsername.executeQuery( "SELECT user_id FROM user_account WHERE user_name = '" + username + "'" );
        		if( rscu.next() == true )
        				return createResponse( "Username already exists.");
        		stCheckUsername.close();
        		rscu.close();


    		//determine correct db value for role
    		String roleStr = null;
    		if( role.compareTo( "Data Manager" ) == 0 ){
    			roleStr = "coordinator";
    		}
    		else if( role.compareTo("Study Director") == 0 ){
    			roleStr = "director";
    		}
    		else if( role.compareTo("Data Specialist") == 0 ){
    			roleStr = role;
    		}
    		else if( role.compareTo("Monitor") == 0 ){
    			roleStr = "monitor";
    		}
        	else if( role.compareTo("Data Entry Person") == 0 ){
        		roleStr = "ra";
        	}
        	else{
        		return createResponse( "No valid role!" );
        	}

        	//write user_account
        		Statement stInsert = m_postGresConn.createStatement();
        		stInsert.execute("INSERT INTO user_account" +  
        				" ( user_id, user_name, passwd, first_name, last_name, email, active_study, institutional_affiliation, " +
        				"status_id, " +		// studien status?
        				"owner_id, " +		//angelegt von (root)
        				"user_type_id," +	// 1-admin 2-user 3-tech-admin
        				"run_webservices ) VALUES " +
        				"('" + m_user_id + "','" + username + "','" + m_pw + "','" + firstname + "','" + lastname + "','" + email +
        				"','" + m_study_id + "','" +  institut + "', '1' , '1' ,'" + type + "','" + soap + "')" );
        		stInsert.close();
        		
        				//enable, account_non_locked, lock_counter, run-webservices
						//true, true, 0 false //default-values

        	//write study_user_role
        		Statement stStudyUserRole = m_postGresConn.createStatement();
        		stStudyUserRole.execute("INSERT INTO study_user_role" +
        								"(role_name, study_id, status_id, user_name) VALUES " +
        								"('" + roleStr + "', '" + m_study_id + "', '1', '" + username + "')");
        		stStudyUserRole.close();

        	//grant authority
        	//getStudyID
        		String authID = null;
        		Statement stGetAuthID = m_postGresConn.createStatement();
        		ResultSet rsauthid= stGetAuthID.executeQuery( "SELECT max(id) FROM authorities" ); 
        		rsauthid.next();
        		authID = rsauthid.getString(1);
        		stGetAuthID.close();
        		rsauthid.close();

        		Statement stGrantAuth = m_postGresConn.createStatement();
        		stGrantAuth.execute("INSERT INTO authorities" +
        								"(id, username, authority, version) VALUES " +
        								"('" + authID + "', '" + username + "', 'ROLE_USER', '0' )");
        		stGrantAuth.close();

            	/*************************************************************************************/

        		m_postGresConn.commit();
            } catch (ClassNotFoundException cnfe) {
            	logger.info( "Couldn't find driver class \n" + cnfe );
            	return createResponse( "Could not find driver class. Pleas check config.properties. \n" + cnfe );
            } catch ( SQLException se) {
            	try {m_postGresConn.rollback();} catch (Exception e) {} 
            	logger.info( "SQL ERROR: \n" + se);
            	return createResponse( "SQL EXEPTION: \n" + se );
            } finally {
            	try {m_postGresConn.close();} catch (Exception e) {}
            }

/*
        if ( m_postGresConn != null ){
        	
        	//getStudyID
        	try {
        		Statement stGetStudyID = m_postGresConn.createStatement();
        		ResultSet rssid = stGetStudyID.executeQuery( "SELECT study_id FROM study WHERE unique_identifier = '" + study + "'" ); 
        		while( rssid.next() ) {
        			m_study_id = rssid.getString(1);
        		}
        		logger.info( "INFO: " + m_study_id );
        	} catch (SQLException e) {
        		logger.info("Could not get study_id FROM study");
        		return createResponse( "Could not get study_id FROM study: \n" + e ); 
        	}
        	if( m_study_id == null ) return createResponse( "study-id '"+ study + "' does not exist: \n" );
        		
        	//getUserID
        	try{
        		Statement stGetUserID = m_postGresConn.createStatement();
        		ResultSet rsuid = stGetUserID.executeQuery( "SELECT MAX(user_id) FROM user_account" );
        		while( rsuid.next() ) {
        			int tmp = Integer.parseInt( rsuid.getString(1) ) + 1;
        			m_user_id = String.valueOf(tmp);
        			logger.info( "INFO rsuid: " + rsuid.getInt(1) );
        			logger.info( "INFO uid: " + m_user_id);
        		}
        	} catch (SQLException e) {
        		logger.info("Could not determine max(user_id) FROM user_account");
        		return createResponse( "Could not determine max(user_id) FROM user_account: " + e );
        	}
        	
        	//check multiple username
        	try{
        		Statement stCheckUsername = m_postGresConn.createStatement();
        		ResultSet rscu = stCheckUsername.executeQuery( "SELECT user_id FROM user_account WHERE user_name = '" + username + "'" );
        		if( rscu.next() == true ) return createResponse( "Username already exists.");
        	} catch (SQLException e) {
        		logger.info("Could not INSERT INTO user_account");
        		return createResponse( "Could not INSERT INTO user_account: " + e );
        	}

    		//determine correct db values
    		String roleStr = null;
    		if( role.compareTo( "Data Manager" ) == 0 ){
    			roleStr = "coordinator";
    		}
    		else if( role.compareTo("Study Director") == 0 ){
    			roleStr = "director";
    		}
    		else if( role.compareTo("Data Specialist") == 0 ){
    			roleStr = role;
    		}
    		else if( role.compareTo("Monitor") == 0 ){
    			roleStr = "monitor";
    		}
        	else if( role.compareTo("Data Entry Person") == 0 ){
        		roleStr = "ra";
        	}
        	else{
        		return createResponse( "No valid role!" );
        	}
        	


        	//write user_account
        	try{
        		Statement stInsert = m_postGresConn.createStatement();
        		stInsert.execute("INSERT INTO user_account" +  
        				" ( user_id, user_name, passwd, first_name, last_name, email, active_study, institutional_affiliation, " +
        				"status_id, " +		// studien status?
        				"owner_id, " +		//angelegt von (root)
        				"user_type_id," +	// 1-admin 2-user 3-tech-admin
        				"run_webservices ) VALUES " +
        				"('" + m_user_id + "','" + username + "','" + m_pw + "','" + firstname + "','" + lastname + "','" + email +
        				"','" + m_study_id + "','" +  institut + "', '1' , '1' ,'" + type + "','" + soap + "')" );
        		
        				//enable, account_non_locked, lock_counter, run-webservices
						//true, true, 0 false //default-values
        	} catch (SQLException e) {
        		logger.info("Could not INSERT INTO user_account");
        		return createResponse( "Could not INSERT INTO user_account: " + e );
        	}

        	//write study_user_role
        	try{
        		Statement stStudyUserRole = m_postGresConn.createStatement();
        		stStudyUserRole.execute("INSERT INTO study_user_role" +
        								"(role_name, study_id, status_id, user_name) VALUES " +
        								"('" + roleStr + "', '" + m_study_id + "', '1', '" + username + "')");
        	} catch (SQLException e) {
        		logger.info("Could not INSERT INTO study_user_role");
        		return createResponse( "Could not INSERT INTO study_user_role: " + e );
        	}

        	//grant authority
        	//getStudyID
        	String authID = null;
        	try {
        		Statement stGetAuthID = m_postGresConn.createStatement();
        		ResultSet rssid = stGetAuthID.executeQuery( "SELECT max(id) FROM authorities" ); 
        		while( rssid.next() ) {
        			authID = rssid.getString(1);
        		}
        	} catch (SQLException e) {
        		logger.info("Could not determine max(id) FROM authorities");
        		return createResponse( "Could not determine max(id) FROM authorities: " + e );
        	}
        	
        	try{
        		Statement stGrantAuth = m_postGresConn.createStatement();
        		stGrantAuth.execute("INSERT INTO authorities" +
        								"(id, username, authority, version) VALUES " +
        								"('" + authID + "', '" + username + "', 'ROLE_USER', '0' )");

        	} catch (SQLException e) {
        		logger.info("Could not INSERT INTO authorities");
        		return createResponse( "Could not INSERT INTO authorities: " + e );
        	}
        }
        */
        

        
        logger.info("db access success");
        return createResponse( "success" );
    }

    /**
     * Berechnung SHA1 Verschluesselung
     * @param text
     * @return
     * @throws NoSuchAlgorithmException
     * @throws UnsupportedEncodingException
     */
    public static String SHA1(String text) throws NoSuchAlgorithmException, UnsupportedEncodingException  { 
    	MessageDigest md;
    	md = MessageDigest.getInstance("SHA-1");
    	byte[] sha1hash = new byte[40];
    	md.update(text.getBytes("iso-8859-1"), 0, text.length());
    	sha1hash = md.digest();
    	return convertToHex(sha1hash);
    }
    
    private static String convertToHex(byte[] data) { 
        StringBuffer buf = new StringBuffer();
        for (int i = 0; i < data.length; i++) { 
            int halfbyte = (data[i] >>> 4) & 0x0F;
            int two_halfs = 0;
            do { 
                if ((0 <= halfbyte) && (halfbyte <= 9)) 
                    buf.append((char) ('0' + halfbyte));
                else 
                    buf.append((char) ('a' + (halfbyte - 10)));
                halfbyte = data[i] & 0x0F;
            } while(two_halfs++ < 1);
        } 
        return buf.toString();
    }
    
    private Element createResponse( String result){
    	String NAMESPACE_URI = "http://imise.de/ur/schemas";
    	Element response = new Element( "createUserResponse", NAMESPACE_URI);
        response.addContent(new Element("response", NAMESPACE_URI).addContent( result ));
        
        return response;
    }
}


//"date_created" +						//date
//"date_updated" +						//date
//"date_lastvisited" +					//timestamp + timezone
//"passwd_timestamp" +					//date
//"passwd_challenge_question" +			//string
//"passwd_challenge_answer" +				//string
//"phone" +								//string
//"user_type_id" +						//int
//"update_id" +							//int		geaendert von 

//ResultSet rs = stGetCount.executeQuery("SELECT * FROM user_account");
//ResultSet rs = stGetCount.executeQuery("INSERT INTO user_account" +
//										//"oid" +
//										"user_id" +								//string [PK]
//										"user_name" +							//string
//										"passwd" +								//string
//										"first_name" +							//string
//										"last_name" +							//string
//										"email" +								//string
//										"active_study" +						//int
//										"institutional_affiliation" +			//string
//										"status_id" +							//int
//										"owner_id" +							//int
//										"date_created" +						//date
//										"date_updated" +						//date
//										"date_lastvisited" +					//timestamp + timezone
//										"passwd_timestamp" +					//date
//										"passwd_challenge_question" +			//string
//										"passwd_challenge_answer" +				//string
//										"phone" +								//string
//										"user_type_id" +						//int
//										"update_id" +							//int
//										"enable" +								//bool	true
//										"account_non_locked" +					//bool	true
//										"lock_counter" +						//int   0
//										"run_webservices"						//bool
//										);

//while (rs.next()) {
    // Get the data from the row using the column index
//    String s = rs.getString(1);
//    logger.info( s );
//}